<?php

namespace service;

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Writer\Exception;
use Throwable;

/**
 * Excel Service
 * Class PHPExcelService
 * @package service
 */
class PHPExcelService
{
    /**
     * @var int 行宽
     */
    protected static $width = 6;

    /**
     * @var int 行高
     */
    protected static $height = 22;

    /**
     * @var string[] 表行名
     */
    private static $cellKey = [
        'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M',
        'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z',
        'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM',
        'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ'
    ];

    /**
     * @var array 设置style
     */
    private static $styleArray = [
        'font' => [
            'size' => '12',
            'name' => '宋体',
            'bold' => false,
        ],
        'alignment' => [
            'wrapText' => true,
            'vertical' => Alignment::VERTICAL_CENTER, // 垂直居中
            'horizontal' => Alignment::HORIZONTAL_CENTER, // 水平居中
        ],
        'borders' => [
            'allBorders' => [
                'borderStyle' => Border::BORDER_THIN,
            ],
        ],
    ];

    /**
     * 公共头部设置
     * Author: Albert3306
     * Date: 2020/11/4
     * @param $sheet
     * @param $data
     * @param string $title
     * @param string[] $column
     * @throws throwable
     */
    private static function setBaseHeader($sheet, $data, $title = '', $column = ['J', 'K'])
    {
        // 表头设置
        $sheet->getStyle('A6:' . $column[1] . (count($data) + 9))->applyFromArray(self::$styleArray);
        $sheet->setCellValue('A1', $title);
        $sheet->getStyle('A1')->getFont()->setName('宋体')->setBold(true)->setSize(18);
        $sheet->getStyle('A1:' . $column[1] . '5')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER)->setVertical(Alignment::VERTICAL_CENTER);

        // 单元宽度设置
        $sheet->getColumnDimension('A')->setWidth(5);
        $sheet->getColumnDimension('B')->setWidth(40);
        $sheet->getColumnDimension($column[0])->setWidth(20);
        $sheet->getColumnDimension($column[1])->setWidth(20);
        $sheet->getDefaultRowDimension()->setRowHeight(self::$height);

        // 表头设置
        $header = [
            ['name' => '表号', 'content' => 'JL676表'],
            ['name' => '制表机关', 'content' => '吉林省畜牧业管理局'],
            ['name' => '文号', 'content' => '吉统审字〔2017〕3号'],
            ['name' => '批准机关', 'content' => '吉林省统计局'],
            ['name' => '有效期至', 'content' => date('Y') . '-12-31'],
        ];
        foreach ($header as $key => $val) {
            $sheet->setCellValue($column[0] . ($key + 1), $val['name'] . '：')->setCellValue($column[1] . ($key + 1), $val['content']);
            $sheet->getStyle($column[0] . ($key + 1))->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
            $sheet->getStyle($column[1] . ($key + 1))->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
        }

        // 签字部位设置
        $sheet->setCellValue('A5', '填报单位');
        $sheet->getStyle('A5')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
        $sheet->mergeCells('A5:E5');
        $sheet->setCellValue('F5', '年');
        $sheet->getStyle('F5')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
        $sheet->setCellValue('I5', '季度');
        $sheet->getStyle('I5')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);

        // 地区设置
        $sheet->setCellValue('A6', '序号');
        $sheet->mergeCells('A6:A9');
        $sheet->setCellValue('B6', '地区');
        $sheet->mergeCells('B6:B9');

        // 导出数据设置
        $i = 10;
        foreach ($data as $item) {
            foreach ($item as $key => $val) {
                $sheet->setCellValue(self::$cellKey[$key] . $i, $val);
            }
            $sheet->getStyle('B' . $i)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
            $i++;
        }
    }

    /**
     * 生产统计月表导出
     * Author: Albert3306
     * Date: 2020/11/6
     * @param $data
     * @param string $title
     * @return string
     * @throws Exception
     * @throws throwable
     */
    public static function exportMonthData($data, $title = '生产统计月表数据导出')
    {
        // 获取Spreadsheet对象
        $spreadsheet = new Spreadsheet();
        self::SetSheet1($spreadsheet, $data[0]);
        self::SetSheet2($spreadsheet, $data[1]);
        self::SetSheet3($spreadsheet, $data[2]);
        self::SetSheet4($spreadsheet, $data[3]);
        self::SetSheet5($spreadsheet, $data[4]);
        self::SetSheet6($spreadsheet, $data[5]);
        self::SetSheet7($spreadsheet, $data[6]);
        self::SetSheet8($spreadsheet, $data[7]);
        self::SetSheet9($spreadsheet, $data[8]);
        self::SetSheet10($spreadsheet, $data[9]);
        self::SetSheet11($spreadsheet, $data[10]);
        self::SetSheet12($spreadsheet, $data[11]);
        self::SetSheet13($spreadsheet, $data[12]);

        // 创建导出文件
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $filename = $title . '--' . time() . '.xlsx';
        $writer->save($filename);
        return $filename;
    }

    /**
     * 月表 Sheet1
     * Author: Albert3306
     * Date: 2020/11/4
     * @param $spreadsheet
     * @param $data
     * @throws throwable
     */
    private static function SetSheet1($spreadsheet, $data)
    {
        // 公共表头设置
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('表1');
        self::setBaseHeader($sheet, $data, '畜牧业生产情况统计表（一）');
        $sheet->mergeCells('A1:I2');

        // 数据头设置
        $sheet->setCellValue('C6', '生猪（头）');
        $sheet->mergeCells('C6:K6');
        $sheet->setCellValue('C7', '存栏合计');
        $sheet->mergeCells('C7:C9');
        $sheet->setCellValue('D7', '其中');
        $sheet->mergeCells('D7:H7');
        $sheet->setCellValue('D8', '能繁殖母猪');
        $sheet->mergeCells('D8:D9');
        $sheet->setCellValue('E8', '后备母猪');
        $sheet->mergeCells('E8:E9');
        $sheet->setCellValue('F8', '累计产仔存活');
        $sheet->mergeCells('F8:F9');
        $sheet->setCellValue('G8', '成龄种公猪');
        $sheet->mergeCells('G8:G9');
        $sheet->setCellValue('H8', '后备种公猪');
        $sheet->mergeCells('H8:H9');
        $sheet->setCellValue('I7', '累计出栏肉用猪');
        $sheet->mergeCells('I7:I9');
        $sheet->setCellValue('J7', '其中');
        $sheet->mergeCells('J7:K7');
        $sheet->setCellValue('J8', '累计出栏猪销往省内');
        $sheet->mergeCells('J8:J9');
        $sheet->setCellValue('K8', '累计出栏猪销往省外');
        $sheet->mergeCells('K8:K9');
    }

    /**
     * 月表 Sheet2
     * Author: Albert3306
     * Date: 2020/11/4
     * @param $spreadsheet
     * @param $data
     * @throws throwable
     */
    private static function SetSheet2($spreadsheet, $data)
    {
        // 公共表头设置
        $sheet = $spreadsheet->createSheet(2);
        $sheet->setTitle('表2');
        self::setBaseHeader($sheet, $data, '畜牧业生产情况统计表（二）', ['Q', 'R']);
        $sheet->mergeCells('A1:P2');

        // 数据头设置
        $sheet->setCellValue('C6', '家禽（千只）');
        $sheet->mergeCells('C6:O6');
        $sheet->setCellValue('C7', '存栏合计');
        $sheet->mergeCells('C7:C9');
        $sheet->setCellValue('D7', '1.');
        $sheet->setCellValue('D8', '鸡存栏');
        $sheet->mergeCells('D8:D9');
        $sheet->setCellValue('E8', '专用蛋鸡');
        $sheet->mergeCells('E8:E9');
        $sheet->setCellValue('E7', '其中');
        $sheet->mergeCells('E7:G7');
        $sheet->setCellValue('F8', '专用肉鸡');
        $sheet->mergeCells('F8:F9');
        $sheet->setCellValue('G8', '兼用鸡');
        $sheet->mergeCells('G8:G9');
        $sheet->setCellValue('H7', '2.');
        $sheet->setCellValue('H8', '鸭存栏');
        $sheet->mergeCells('H8:H9');
        $sheet->setCellValue('I7', '3.');
        $sheet->setCellValue('I8', '鹅存栏');
        $sheet->mergeCells('I8:I9');
        $sheet->setCellValue('J7', '累计出栏肉用家禽');
        $sheet->mergeCells('J7:J9');
        $sheet->setCellValue('K7', '其中');
        $sheet->mergeCells('K7:O7');
        $sheet->setCellValue('K8', '肉鸡出栏');
        $sheet->mergeCells('K8:K9');
        $sheet->setCellValue('L8', '蛋鸡出栏');
        $sheet->mergeCells('L8:L9');
        $sheet->setCellValue('M8', '兼用鸡');
        $sheet->mergeCells('M8:M9');
        $sheet->setCellValue('N8', '鸭出栏');
        $sheet->mergeCells('N8:N9');
        $sheet->setCellValue('O8', '鹅出栏');
        $sheet->mergeCells('O8:O9');
        $sheet->setCellValue('P6', '家兔（只）');
        $sheet->mergeCells('P6:R6');
        $sheet->setCellValue('P7', '存栏合计');
        $sheet->mergeCells('P7:P9');
        $sheet->setCellValue('Q7', '累计出栏肉用兔');
        $sheet->mergeCells('Q7:Q9');
        $sheet->setCellValue('R7', '兔皮产量（张）');
        $sheet->mergeCells('R7:R9');
    }

    /**
     * 月表 Sheet3
     * Author: Albert3306
     * Date: 2020/11/4
     * @param $spreadsheet
     * @param $data
     * @throws throwable
     */
    private static function SetSheet3($spreadsheet, $data)
    {
        // 公共表头设置
        $sheet = $spreadsheet->createSheet(3);
        $sheet->setTitle('表3');
        self::setBaseHeader($sheet, $data, '畜牧业生产情况统计表（三）', ['L', 'M']);
        $sheet->mergeCells('A1:K2');

        // 数据头设置
        $sheet->setCellValue('C6', '大牲畜（头、匹）');
        $sheet->mergeCells('C6:M6');
        $sheet->setCellValue('C7', '存栏合计');
        $sheet->mergeCells('C7:C9');
        $sheet->setCellValue('D7', '其中：' . PHP_EOL . '1.从事农业劳役');
        $sheet->mergeCells('D7:D9');
        $sheet->setCellValue('E7', '2.牛');
        $sheet->mergeCells('E7:G7');
        $sheet->setCellValue('E8', '存栏合计');
        $sheet->mergeCells('E8:E9');
        $sheet->setCellValue('F8', '能繁殖母牛');
        $sheet->mergeCells('F8:F9');
        $sheet->setCellValue('G8', '累计产仔成活');
        $sheet->mergeCells('G8:G9');
        $sheet->setCellValue('H7', '（1）肉牛');
        $sheet->mergeCells('H7:J7');
        $sheet->setCellValue('H8', '存栏合计');
        $sheet->mergeCells('H8:H9');
        $sheet->setCellValue('I8', '能繁殖母牛');
        $sheet->mergeCells('I8:I9');
        $sheet->setCellValue('J8', '累计产仔成活');
        $sheet->mergeCells('J8:J9');
        $sheet->setCellValue('K7', '（2）奶牛');
        $sheet->mergeCells('K7:M7');
        $sheet->setCellValue('K8', '存栏合计');
        $sheet->mergeCells('K8:K9');
        $sheet->setCellValue('L8', '能繁殖母牛');
        $sheet->mergeCells('L8:L9');
        $sheet->setCellValue('M8', '累计产仔成活');
        $sheet->mergeCells('M8:M9');
    }

    /**
     * 月表 Sheet4
     * Author: Albert3306
     * Date: 2020/11/4
     * @param $spreadsheet
     * @param $data
     * @throws throwable
     */
    private static function SetSheet4($spreadsheet, $data)
    {
        // 公共表头设置
        $sheet = $spreadsheet->createSheet(4);
        $sheet->setTitle('表4');
        self::setBaseHeader($sheet, $data, '畜牧业生产情况统计表（四）', ['O', 'P']);
        $sheet->mergeCells('A1:N2');

        // 数据头设置
        $sheet->setCellValue('C6', '大牲畜（头、匹）');
        $sheet->mergeCells('C6:P6');
        $sheet->setCellValue('C7', '2.马');
        $sheet->mergeCells('C7:E7');
        $sheet->setCellValue('C8', '存栏合计');
        $sheet->mergeCells('C8:C9');
        $sheet->setCellValue('D8', '能繁殖母马');
        $sheet->mergeCells('D8:D9');
        $sheet->setCellValue('E8', '累计产仔成活');
        $sheet->mergeCells('E8:E9');
        $sheet->setCellValue('F7', '3.驴');
        $sheet->mergeCells('F7:G7');
        $sheet->setCellValue('F8', '存栏合计');
        $sheet->mergeCells('F8:F9');
        $sheet->setCellValue('G8', '能繁殖母驴');
        $sheet->mergeCells('G8:G9');
        $sheet->setCellValue('H8', '累计产仔成活');
        $sheet->mergeCells('H8:H9');
        $sheet->setCellValue('I7', '4.骡');
        $sheet->mergeCells('I7:J7');
        $sheet->setCellValue('I8', '存栏合计');
        $sheet->mergeCells('I8:I9');
        $sheet->setCellValue('J8', '累计成活仔骡');
        $sheet->mergeCells('J8:J9');
        $sheet->setCellValue('K7', '累计出栏肉用大牲畜');
        $sheet->mergeCells('K7:K9');
        $sheet->setCellValue('L7', '其中：');
        $sheet->setCellValue('L8', '1.从事农业劳役');
        $sheet->mergeCells('L8:L9');
        $sheet->setCellValue('M7', '2.');
        $sheet->setCellValue('M8', '牛出栏');
        $sheet->mergeCells('M8:M9');
        $sheet->setCellValue('N7', '其中');
        $sheet->mergeCells('N7:P7');
        $sheet->setCellValue('N8', '累计销往省外');
        $sheet->mergeCells('N8:N9');
        $sheet->setCellValue('O8', '（1）肉牛出栏');
        $sheet->mergeCells('O8:O9');
        $sheet->setCellValue('P8', '（2）奶牛出栏');
        $sheet->mergeCells('P8:P9');
    }

    /**
     * 月表 Sheet5
     * Author: Albert3306
     * Date: 2020/11/4
     * @param $spreadsheet
     * @param $data
     * @throws throwable
     */
    private static function SetSheet5($spreadsheet, $data)
    {
        // 公共表头设置
        $sheet = $spreadsheet->createSheet(5);
        $sheet->setTitle('表5');
        self::setBaseHeader($sheet, $data, '畜牧业生产情况统计表（五）', ['L', 'M']);
        $sheet->mergeCells('A1:K2');

        // 数据头设置
        $sheet->setCellValue('C6', '大牲畜（头、匹）');
        $sheet->mergeCells('C6:E6');
        $sheet->setCellValue('C7', '2.');
        $sheet->setCellValue('D7', '3.');
        $sheet->setCellValue('E7', '4.');
        $sheet->setCellValue('C8', '马出栏');
        $sheet->mergeCells('C8:C9');
        $sheet->setCellValue('D8', '驴出栏');
        $sheet->mergeCells('D8:D9');
        $sheet->setCellValue('E8', '骡出栏');
        $sheet->mergeCells('E8:E9');
        $sheet->setCellValue('F6', '羊（只）');
        $sheet->mergeCells('F6:M6');
        $sheet->setCellValue('F7', '存栏合计');
        $sheet->mergeCells('F7:F9');
        $sheet->setCellValue('G7', '其中');
        $sheet->mergeCells('G7:H7');
        $sheet->setCellValue('G8', '能繁殖母羊');
        $sheet->mergeCells('G8:G9');
        $sheet->setCellValue('H8', '累计产仔成活');
        $sheet->mergeCells('H8:H9');
        $sheet->setCellValue('I7', '1.');
        $sheet->setCellValue('I8', '绵羊存栏');
        $sheet->mergeCells('I8:I9');
        $sheet->setCellValue('J7', '其中');
        $sheet->mergeCells('J7:M7');
        $sheet->setCellValue('J8', '能繁殖母羊');
        $sheet->mergeCells('J8:J9');
        $sheet->setCellValue('K8', '累计产仔成活');
        $sheet->mergeCells('K8:K9');
        $sheet->setCellValue('L8', '细毛羊及其改良羊');
        $sheet->mergeCells('L8:L9');
        $sheet->setCellValue('M8', '半细毛羊及其改良羊');
        $sheet->mergeCells('M8:M9');
    }

    /**
     * 月表 Sheet6
     * Author: Albert3306
     * Date: 2020/11/4
     * @param $spreadsheet
     * @param $data
     * @throws throwable
     */
    private static function SetSheet6($spreadsheet, $data)
    {
        // 公共表头设置
        $sheet = $spreadsheet->createSheet(6);
        $sheet->setTitle('表6');
        self::setBaseHeader($sheet, $data, '畜牧业生产情况统计表（六）', ['M', 'N']);
        $sheet->mergeCells('A1:L2');

        // 数据头设置
        $sheet->setCellValue('C6', '羊（只）');
        $sheet->mergeCells('C6:I6');
        $sheet->setCellValue('C7', '2.');
        $sheet->setCellValue('C8', '山羊存栏');
        $sheet->mergeCells('C8:C9');
        $sheet->setCellValue('D7', '其中');
        $sheet->mergeCells('D7:E7');
        $sheet->setCellValue('D8', '能繁殖母羊');
        $sheet->mergeCells('D8:D9');
        $sheet->setCellValue('E8', '累计产仔成活');
        $sheet->mergeCells('E8:E9');
        $sheet->setCellValue('F7', '累计出栏肉用羊');
        $sheet->mergeCells('F7:F9');
        $sheet->setCellValue('G7', '其中');
        $sheet->mergeCells('G7:I7');
        $sheet->setCellValue('G8', '1.');
        $sheet->setCellValue('G9', '绵羊');
        $sheet->setCellValue('H8', '2.');
        $sheet->setCellValue('H9', '山羊');
        $sheet->setCellValue('I8', '累计出栏销往省外');
        $sheet->mergeCells('I8:I9');
        $sheet->setCellValue('J6', '鹿（只）');
        $sheet->mergeCells('J6:N6');
        $sheet->setCellValue('J7', '存栏合计');
        $sheet->mergeCells('J7:J9');
        $sheet->setCellValue('K7', '其中');
        $sheet->mergeCells('K7:L7');
        $sheet->setCellValue('K8', '能繁殖母鹿');
        $sheet->mergeCells('K8:K9');
        $sheet->setCellValue('L8', '累计产仔成活');
        $sheet->mergeCells('L8:L9');
        $sheet->setCellValue('M7', '累计出栏');
        $sheet->mergeCells('M7:M9');
        $sheet->setCellValue('N7', '其中');
        $sheet->setCellValue('N8', '累计出栏销往省外');
        $sheet->mergeCells('N8:N9');
    }

    /**
     * 月表 Sheet7
     * Author: Albert3306
     * Date: 2020/11/4
     * @param $spreadsheet
     * @param $data
     * @throws throwable
     */
    private static function SetSheet7($spreadsheet, $data)
    {
        // 公共表头设置
        $sheet = $spreadsheet->createSheet(7);
        $sheet->setTitle('表7');
        self::setBaseHeader($sheet, $data, '畜牧业生产情况统计表（七）', ['M', 'N']);
        $sheet->mergeCells('A1:L2');

        // 数据头设置
        $sheet->setCellValue('C6', '养蜂（箱）');
        $sheet->mergeCells('C6:C9');
        $sheet->setCellValue('D6', '犬（只）');
        $sheet->mergeCells('D6:G6');
        $sheet->setCellValue('D7', '存栏合计');
        $sheet->mergeCells('D7:D9');
        $sheet->setCellValue('E7', '其中');
        $sheet->mergeCells('E7:F7');
        $sheet->setCellValue('E8', '能繁殖母犬');
        $sheet->mergeCells('E8:E9');
        $sheet->setCellValue('F8', '累计产仔成活');
        $sheet->mergeCells('F8:F9');
        $sheet->setCellValue('G7', '累计出栏肉用犬');
        $sheet->mergeCells('G7:G9');
        $sheet->setCellValue('H6', '累计畜产品产量（吨）');
        $sheet->mergeCells('H6:N6');
        $sheet->setCellValue('H7', '肉类产量合计');
        $sheet->mergeCells('H7:H9');
        $sheet->setCellValue('I7', '1.');
        $sheet->setCellValue('I8', '猪肉');
        $sheet->mergeCells('I8:I9');
        $sheet->setCellValue('J7', '2.');
        $sheet->setCellValue('J8', '牛肉');
        $sheet->mergeCells('J8:J9');
        $sheet->setCellValue('K7', '3.');
        $sheet->setCellValue('K8', '马肉');
        $sheet->mergeCells('K8:K9');
        $sheet->setCellValue('L7', '4.');
        $sheet->setCellValue('L8', '驴肉');
        $sheet->mergeCells('L8:L9');
        $sheet->setCellValue('M7', '5.');
        $sheet->setCellValue('M8', '骡肉');
        $sheet->mergeCells('M8:M9');
        $sheet->setCellValue('N7', '6.');
        $sheet->setCellValue('N8', '兔肉');
        $sheet->mergeCells('N8:N9');
    }

    /**
     * 月表 Sheet8
     * Author: Albert3306
     * Date: 2020/11/4
     * @param $spreadsheet
     * @param $data
     * @throws throwable
     */
    private static function SetSheet8($spreadsheet, $data)
    {
        // 公共表头设置
        $sheet = $spreadsheet->createSheet(8);
        $sheet->setTitle('表8');
        self::setBaseHeader($sheet, $data, '畜牧业生产情况统计表（八）', ['R', 'S']);
        $sheet->mergeCells('A1:Q2');

        // 数据头设置
        $sheet->setCellValue('C6', '累计畜产品产量（吨）');
        $sheet->mergeCells('C6:S6');
        $sheet->setCellValue('C7', '7.');
        $sheet->setCellValue('C8', '羊肉');
        $sheet->mergeCells('C8:C9');
        $sheet->setCellValue('D7', '其中');
        $sheet->mergeCells('D7:E7');
        $sheet->setCellValue('D8', '（1）绵羊肉');
        $sheet->mergeCells('D8:D9');
        $sheet->setCellValue('E8', '（2）山羊肉');
        $sheet->mergeCells('E8:E9');
        $sheet->setCellValue('F7', '8.');
        $sheet->setCellValue('F8', '家禽肉');
        $sheet->mergeCells('F8:F9');
        $sheet->setCellValue('G7', '（1）鸡肉');
        $sheet->mergeCells('G7:G9');
        $sheet->setCellValue('H7', '其中');
        $sheet->mergeCells('H7:J7');
        $sheet->setCellValue('H8', '①专用型肉鸡');
        $sheet->mergeCells('H8:H9');
        $sheet->setCellValue('I8', '②专用型蛋鸡');
        $sheet->mergeCells('I8:I9');
        $sheet->setCellValue('J8', '③兼用型鸡');
        $sheet->mergeCells('J8:J9');
        $sheet->setCellValue('K7', '（2）鸭肉');
        $sheet->mergeCells('K7:K9');
        $sheet->setCellValue('L7', '（3）鹅肉');
        $sheet->mergeCells('L7:L9');
        $sheet->setCellValue('M7', '9.');
        $sheet->setCellValue('M8', '犬肉');
        $sheet->mergeCells('M8:M9');
        $sheet->setCellValue('N7', '10.');
        $sheet->setCellValue('N8', '鹿肉');
        $sheet->mergeCells('N8:N9');
        $sheet->setCellValue('O7', '禽蛋产量');
        $sheet->mergeCells('O7:O9');
        $sheet->setCellValue('P7', '其中');
        $sheet->mergeCells('P7:R7');
        $sheet->setCellValue('P8', '鸡蛋');
        $sheet->mergeCells('P8:P9');
        $sheet->setCellValue('Q8', '鸭蛋');
        $sheet->mergeCells('Q8:Q9');
        $sheet->setCellValue('R8', '鹅蛋');
        $sheet->mergeCells('R8:R9');
        $sheet->setCellValue('S7', '禽蛋累计销往省外');
        $sheet->mergeCells('S7:S9');
    }

    /**
     * 月表 Sheet9
     * Author: Albert3306
     * Date: 2020/11/4
     * @param $spreadsheet
     * @param $data
     * @throws throwable
     */
    private static function SetSheet9($spreadsheet, $data)
    {
        // 公共表头设置
        $sheet = $spreadsheet->createSheet(9);
        $sheet->setTitle('表9');
        self::setBaseHeader($sheet, $data, '畜牧业生产情况统计表（九）', ['N', 'O']);
        $sheet->mergeCells('A1:M2');

        // 数据头设置
        $sheet->setCellValue('C6', '累计畜产品产量（吨）');
        $sheet->mergeCells('C6:M6');
        $sheet->setCellValue('C7', '柞蚕茧产量');
        $sheet->mergeCells('C7:C9');
        $sheet->setCellValue('D7', '蜂蜜产量');
        $sheet->mergeCells('D7:D9');
        $sheet->setCellValue('E7', '羊毛产量');
        $sheet->mergeCells('E7:E9');
        $sheet->setCellValue('F7', '1.');
        $sheet->setCellValue('F8', '山羊毛');
        $sheet->mergeCells('F8:F9');
        $sheet->setCellValue('G7', '2.');
        $sheet->setCellValue('G8', '绵羊毛');
        $sheet->mergeCells('G8:G9');
        $sheet->setCellValue('H7', '其中');
        $sheet->mergeCells('H7:I7');
        $sheet->setCellValue('H8', '细羊毛');
        $sheet->mergeCells('H8:H9');
        $sheet->setCellValue('I8', '半细羊毛');
        $sheet->mergeCells('I8:I9');
        $sheet->setCellValue('J7', '羊绒产量（千克）');
        $sheet->mergeCells('J7:J9');
        $sheet->setCellValue('K7', '鹿茸产量（千克）');
        $sheet->mergeCells('K7:K9');
        $sheet->setCellValue('L7', '奶类产量');
        $sheet->mergeCells('L7:L9');
        $sheet->setCellValue('M7', '其中');
        $sheet->setCellValue('M8', '牛奶');
        $sheet->mergeCells('M8:M9');
        $sheet->setCellValue('N6', '备注');
        $sheet->mergeCells('N6:O6');
        $sheet->setCellValue('N7', '总人口（万人）');
        $sheet->mergeCells('N7:N9');
        $sheet->setCellValue('O7', '其中');
        $sheet->setCellValue('O8', '农业人口（万人）');
        $sheet->mergeCells('O8:O9');
    }

    /**
     * 月表 Sheet10
     * Author: Albert3306
     * Date: 2020/11/4
     * @param $spreadsheet
     * @param $data
     * @throws throwable
     */
    private static function SetSheet10($spreadsheet, $data)
    {
        // 公共表头设置
        $sheet = $spreadsheet->createSheet(10);
        $sheet->setTitle('表10');
        self::setBaseHeader($sheet, $data, '畜牧业生产情况统计表（十）', ['M', 'N']);
        $sheet->mergeCells('A1:L2');

        // 数据头设置
        $sheet->setCellValue('C6', '狐（只）');
        $sheet->mergeCells('C6:F6');
        $sheet->setCellValue('C7', '存栏');
        $sheet->mergeCells('C7:C9');
        $sheet->setCellValue('D7', '其中');
        $sheet->setCellValue('D8', '能繁母狐');
        $sheet->mergeCells('D8:D9');
        $sheet->setCellValue('E7', '累计出栏');
        $sheet->mergeCells('E7:E9');
        $sheet->setCellValue('F7', '狐皮产量（张）');
        $sheet->mergeCells('F7:F9');
        $sheet->setCellValue('G6', '貂（只）');
        $sheet->mergeCells('G6:J6');
        $sheet->setCellValue('G7', '存栏');
        $sheet->mergeCells('G7:G9');
        $sheet->setCellValue('H7', '其中');
        $sheet->setCellValue('H8', '能繁母貂');
        $sheet->mergeCells('H8:H9');
        $sheet->setCellValue('I7', '累计出栏');
        $sheet->mergeCells('I7:I9');
        $sheet->setCellValue('J7', '貂皮产量（张）');
        $sheet->mergeCells('J7:J9');
        $sheet->setCellValue('K6', '貉（只）');
        $sheet->mergeCells('K6:N6');
        $sheet->setCellValue('K7', '存栏');
        $sheet->mergeCells('K7:K9');
        $sheet->setCellValue('L7', '其中');
        $sheet->setCellValue('L8', '能繁母貉');
        $sheet->mergeCells('L8:L9');
        $sheet->setCellValue('M7', '累计出栏');
        $sheet->mergeCells('M7:M9');
        $sheet->setCellValue('N7', '貉皮产量（张）');
        $sheet->mergeCells('N7:N9');
    }

    /**
     * 月表 Sheet11
     * Author: Albert3306
     * Date: 2020/11/4
     * @param $spreadsheet
     * @param $data
     * @throws throwable
     */
    private static function SetSheet11($spreadsheet, $data)
    {
        // 公共表头设置
        $sheet = $spreadsheet->createSheet(11);
        $sheet->setTitle('表11');
        self::setBaseHeader($sheet, $data, '规模饲养户情况统计表（一）', ['L', 'M']);
        $sheet->mergeCells('A1:K2');

        // 数据头设置
        $sheet->setCellValue('C6', '规模饲养户（场）合计');
        $sheet->mergeCells('C6:C9');
        $sheet->setCellValue('D6', '猪（个、头）');
        $sheet->mergeCells('D6:G6');
        $sheet->setCellValue('D7', '户（场）数');
        $sheet->mergeCells('D7:D9');
        $sheet->setCellValue('E7', '期末存栏');
        $sheet->mergeCells('E7:E9');
        $sheet->setCellValue('F7', '其中');
        $sheet->setCellValue('F8', '存栏母猪');
        $sheet->mergeCells('F8:F9');
        $sheet->setCellValue('G7', '累计出栏肉用猪');
        $sheet->mergeCells('G7:G9');
        $sheet->setCellValue('H6', '肉牛（个、头）');
        $sheet->mergeCells('H6:J6');
        $sheet->setCellValue('H7', '户（场）数');
        $sheet->mergeCells('H7:H9');
        $sheet->setCellValue('I7', '期末存栏');
        $sheet->mergeCells('I7:I9');
        $sheet->setCellValue('J7', '累计出栏肉用牛');
        $sheet->mergeCells('J7:J9');
        $sheet->setCellValue('K6', '奶牛（个、头、吨）');
        $sheet->mergeCells('K6:M6');
        $sheet->setCellValue('K7', '户（场）数');
        $sheet->mergeCells('K7:K9');
        $sheet->setCellValue('L7', '期末存栏');
        $sheet->mergeCells('L7:L9');
        $sheet->setCellValue('M7', '累计牛奶产量');
        $sheet->mergeCells('M7:M9');
    }

    /**
     * 月表 Sheet12
     * Author: Albert3306
     * Date: 2020/11/4
     * @param $spreadsheet
     * @param $data
     * @throws throwable
     */
    private static function SetSheet12($spreadsheet, $data)
    {
        // 公共表头设置
        $sheet = $spreadsheet->createSheet(12);
        $sheet->setTitle('表12');
        self::setBaseHeader($sheet, $data, '规模饲养户情况统计表（二）', ['N', 'O']);
        $sheet->mergeCells('A1:M2');

        // 数据头设置
        $sheet->setCellValue('C6', '羊（个、只）');
        $sheet->mergeCells('C6:E6');
        $sheet->setCellValue('C7', '户（场）数');
        $sheet->mergeCells('C7:C9');
        $sheet->setCellValue('D7', '期末存栏');
        $sheet->mergeCells('D7:D9');
        $sheet->setCellValue('E7', '累计出栏肉用羊');
        $sheet->mergeCells('E7:E9');
        $sheet->setCellValue('F6', '家禽（千只）');
        $sheet->mergeCells('F6:O6');
        $sheet->setCellValue('F7', '户（场）数');
        $sheet->mergeCells('F7:F9');
        $sheet->setCellValue('G7', '其中');
        $sheet->mergeCells('G7:J7');
        $sheet->setCellValue('G8', '蛋鸡');
        $sheet->mergeCells('G8:G9');
        $sheet->setCellValue('H8', '肉鸡');
        $sheet->mergeCells('H8:H9');
        $sheet->setCellValue('I8', '鹅');
        $sheet->mergeCells('I8:I9');
        $sheet->setCellValue('J8', '鸭');
        $sheet->mergeCells('J8:J9');
        $sheet->setCellValue('K7', '蛋鸡存栏');
        $sheet->mergeCells('K7:K9');
        $sheet->setCellValue('L7', '肉鸡存栏');
        $sheet->mergeCells('L7:L9');
        $sheet->setCellValue('M7', '累计出栏肉鸡');
        $sheet->mergeCells('M7:M9');
        $sheet->setCellValue('N7', '累计出栏淘汰蛋鸡');
        $sheet->mergeCells('N7:N9');
        $sheet->setCellValue('O7', '鹅存栏');
        $sheet->mergeCells('O7:O9');
    }

    /**
     * 月表 Sheet13
     * Author: Albert3306
     * Date: 2020/11/4
     * @param $spreadsheet
     * @param $data
     * @throws throwable
     */
    private static function SetSheet13($spreadsheet, $data)
    {
        // 公共表头设置
        $sheet = $spreadsheet->createSheet(13);
        $sheet->setTitle('表13');
        self::setBaseHeader($sheet, $data, '规模饲养户及牧业经济情况统计表', ['K', 'L']);
        $sheet->mergeCells('A1:J2');

        // 数据头设置
        $sheet->setCellValue('C6', '家禽（千只）');
        $sheet->mergeCells('C6:E7');
        $sheet->setCellValue('C8', '累计出栏肉用鹅');
        $sheet->mergeCells('C8:C9');
        $sheet->setCellValue('D8', '鸭存栏');
        $sheet->mergeCells('D8:D9');
        $sheet->setCellValue('E8', '累计出栏肉用鸭');
        $sheet->mergeCells('E8:E9');
        $sheet->setCellValue('F6', '农林牧渔业总产值（亿元）');
        $sheet->mergeCells('F6:F9');
        $sheet->setCellValue('G6', '其中');
        $sheet->setCellValue('G7', '牧业产值（亿元）');
        $sheet->mergeCells('G7:G9');
        $sheet->setCellValue('H6', '牧业产值占农林牧渔业总产值比重（%）');
        $sheet->mergeCells('H6:H9');
        $sheet->setCellValue('I6', '农民人均总收入（元）');
        $sheet->mergeCells('I6:I9');
        $sheet->setCellValue('J6', '其中');
        $sheet->setCellValue('J7', '农民人均牧业收入（元）');
        $sheet->mergeCells('J7:J9');
        $sheet->setCellValue('K6', '农民人均牧业收入占农民人均总收入的比重（%）');
        $sheet->mergeCells('K6:K9');
        $sheet->setCellValue('L6', '农民来自牧业现金收入（元）');
        $sheet->mergeCells('L6:L9');
    }
}